/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package rad.Java.Common.DB;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author rogerio
 */
public class Search
{
    private ArrayList<String> _tables = new ArrayList<String>();
    private ArrayList<String> _fields = new ArrayList<String>();
    private ArrayList<String> _statements  = new ArrayList<String>();
    private ArrayList<String> _conditionalWhere = new ArrayList<String>();
    private ArrayList<String> _junction = new ArrayList<String>();
    private ArrayList<String> _additional = new ArrayList<String>();
    //private GenerateSql _gsql = new GenerateSql();
    private ResultSet _rs;
    private boolean _distinct;
    
    public Search()
    {

    }

    public void prepare()
    {
        _tables.clear();
        _fields.clear();
        _statements.clear();
        _conditionalWhere.clear();
        _junction.clear();
        _additional.clear();
        _distinct = false;
    }

    public void setTable(String value)
    {
        _tables.add(value);
        _statements.add("");
    }

    public void setTable(String table, String onStatement)
    {
        _tables.add(table);
        _statements.add(onStatement);
    }

    public void setOnStatement(String onStatement)
    {
        _statements.add(onStatement);
    }

    public void setConditionalWhere(String value)
    {
        _conditionalWhere.add(value);
    }

    public void setAdditional(String value)
    {
        _additional.add(value);
    }

    public void setFields(String field)
    {
        _fields.add(field);
    }

    public void setFields(String []array)
    {
        for(int i=0; i < array.length; i++)
            _fields.add(array[i]);
    }

    public void setDistinct(boolean value)
    {
        _distinct = value;
    }

    public String getSql()
    {
        return mountSql();
    }

    public ResultSet getResultSet()
    {
        return _rs;
    }

    public Object getValue(String field) throws SQLException
    {
        return _rs.getObject(field);
    }

    public String getMessageError()
    {
        return Database.ErrorMessage;
    }


    public Boolean next() throws SQLException
    {
        return _rs.next();
    }

    public Boolean execute()
    {
        String sql = mountSql();

        _rs = Database.executeQueryRs(sql);
        return !Database.Error;
    }

    private String mountSql()
    {
        String sSQL;
        String joins;
        String sWhere;
        String aux;
        String additional = "";
        String distinct="";

        /* get name of fields*/
        sSQL = "";
        for (int i = 0; i < _fields.size(); i++)
        {
            if (!_fields.get(i).equals(""))
            {
                if (sSQL.equals(""))
                        sSQL = _fields.get(i);
                else
                        sSQL = sSQL + ", " + _fields.get(i);
            }
        }

        /*get joins*/
        joins = _tables.get(0);
        for(int i=1; i < _tables.size(); i++)
        {
            joins+= " JOIN "+_tables.get(i)+" ON "+_statements.get(i);
        }

         /* get where*/
        if (_conditionalWhere.size() != 0)
        {
                sWhere = " WHERE " + _conditionalWhere.get(0);
                for (int i = 1; i < _conditionalWhere.size(); i++)
                {
                        if (_junction.size() >= i)
                                aux = _junction.get(i-1);
                        else
                                aux = "AND";
                        sWhere += " "+aux+" "+_conditionalWhere.get(i);
                }
        }
        else
                sWhere = "";

        //additional
        for (int i=0; i < _additional.size(); i++)
        {
                additional+= " "+_additional.get(i);
        }

        if(_distinct)
            distinct = " DISTINCT ";
        
        return "SELECT " + distinct + " "+sSQL + " FROM "+ joins + sWhere + " " +additional;
    }

    public boolean isFirstRow()
    {
        try {
            return _rs.isFirst();
        } catch (SQLException ex) {
            Logger.getLogger(Search.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }
}
